﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DDF.Data.Providers;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using DDF.Data;
using ARR.Entities.Gateways;

namespace ARR.DataModel.Repositories
{
    public static partial class TempArticlesRepository
    {
        public static void TruncateTable()
        {
            using (SqlDataProvider database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;
                database.NewCommand("TRUNCATE TABLE TempArticles ", System.Data.CommandType.Text);
                database.ExecuteNonQuery();
            }
        }

        public static void BulkCopy(DataTable articlesToCopy)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
                {
                    bulk.BulkCopyTimeout = 300;

                    bulk.DestinationTableName = "TempArticles";

                    bulk.ColumnMappings.Add("RangeReviewId", "RangeReviewId");
                    bulk.ColumnMappings.Add("Code", "Code");
                    bulk.ColumnMappings.Add("Name", "Name");
                    bulk.ColumnMappings.Add("EAN", "EAN");
                    bulk.ColumnMappings.Add("UOM", "UOM");
                    bulk.ColumnMappings.Add("Brand", "Brand");
                    bulk.ColumnMappings.Add("Manufacturer", "Manufacturer");
                    bulk.ColumnMappings.Add("NotFoundCategory", "NotFoundCategory");
                    bulk.ColumnMappings.Add("Size", "Size");
                    bulk.ColumnMappings.Add("Width", "Width");
                    bulk.ColumnMappings.Add("Height", "Height");
                    bulk.ColumnMappings.Add("Facings", "Facings");
                    bulk.ColumnMappings.Add("Attribute1", "Attribute1");
                    bulk.ColumnMappings.Add("Attribute2", "Attribute2");
                    bulk.ColumnMappings.Add("Attribute3", "Attribute3");
                    bulk.ColumnMappings.Add("Attribute4", "Attribute4");
                    bulk.ColumnMappings.Add("Attribute5", "Attribute5");
                    bulk.ColumnMappings.Add("TaxRate", "TaxRate");
                    bulk.ColumnMappings.Add("Type", "Type");
                    bulk.ColumnMappings.Add("OrderUOM", "OrderUOM");
                    bulk.ColumnMappings.Add("OrderEAN", "OrderEAN");
                    bulk.ColumnMappings.Add("Pack", "Pack");
                    bulk.ColumnMappings.Add("Vendor", "Vendor");
                    bulk.ColumnMappings.Add("Price", "Price");
                    bulk.ColumnMappings.Add("NetPrice", "NetPrice");
                    bulk.ColumnMappings.Add("CreatedDate", "CreatedDate");
                    bulk.ColumnMappings.Add("Cost", "Cost");

                    bulk.WriteToServer(articlesToCopy);
                    bulk.Close();
                }

                conn.Close();
            }
        }

        public static void UpdateCategoryIds()
        {
            using (SqlDataProvider database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;
                database.NewCommand("TempArticles_UpdateCategoryIds", CommandType.StoredProcedure);
                database.ExecuteNonQuery();
            }
        }

        public static void DeleteDuplicates()
        {
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 300;

                database.NewCommand("TempArticles_DeleteDuplicates", CommandType.StoredProcedure);
                database.ExecuteNonQuery();
            }
        }

        public static void InsertDataFromTempSales()
        {
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;

                database.NewCommand("TempArticles_InsertDataFromTempSales", CommandType.StoredProcedure);
                database.ExecuteNonQuery();
            }
        }

        public static void UpdateCategoryMatch(int categoryId, string[] originalNames)
        {
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.NewCommand("TempArticle_UpdateCategoryMatch", CommandType.StoredProcedure);
                database.AddInParameter("CategoryId", DbType.Int16, categoryId);
                database.AddInParameter("OriginalCategories", DbType.String, String.Join(",", originalNames));

                database.ExecuteNonQuery();
            }

        }

        public static void CombineRepeatedNamesAndUpdateSales(string[] repeatedArticles)
        {
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.NewCommand("TempArticles_CombineRepeatedNamesAndUpdateSales", CommandType.StoredProcedure);
                database.AddInParameter("@ArticleCodes", DbType.String, String.Join(";", repeatedArticles));

                database.ExecuteNonQuery();
            }
        }

        public static List<TempArticleRow> GetSameStoreArticles(int storeId)
        {
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.NewCommand("TempArticles_SameStoreArticles", CommandType.StoredProcedure);
                database.AddInParameter("sameStoreId", DbType.Int32, storeId);

                IDataReader reader = database.ExecuteReader();

                List<TempArticleRow> tempArticles = new List<TempArticleRow>();

                LoadArticles(reader, tempArticles);

                return tempArticles;
            }
        }

        public static void ConcatenateUomAndCodeForRepeatedCodes()
        {
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.NewCommand("TempArticles_ConcatenateUomAndCodeForRepeatedCodes", CommandType.StoredProcedure);
                
                database.ExecuteNonQuery();
            }
        }

        public static void ConcatenateUomAndCodeForRepeatedNames()
        {
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.NewCommand("TempArticles_ConcatenateUomAndCodeForRepeatedNames", CommandType.StoredProcedure);

                database.ExecuteNonQuery();
            }
        }

        private static void LoadArticles(IDataReader reader, List<TempArticleRow> tempArticles)
        {
            TempArticleRow row;

            while (reader.Read())
            {
                row = new TempArticleRow()
                {
                    Id = Util.CastDbValue<int>(reader["Id"]),
                    Name = Util.CastDbValue<string>(reader["Name"]),
                    Code = Util.CastDbValue<string>(reader["Code"]),
                    EAN = Util.CastDbValue<string>(reader["Code"]),
                    UOM = Util.CastDbValue<string>(reader["UOM"]),
                    Brand = Util.CastDbValue<string>(reader["Brand"]),
                    Manufacturer = Util.CastDbValue<string>(reader["Manufacturer"]),
                    RangeReviewId = Util.CastDbValue<int>(reader["RangeReviewId"])
                };

                tempArticles.Add(row);
            }

            reader.Close();
        }


    }
}
